<!DOCTYPE html>
<html lang="zh-CN">
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <meta name="robots" content="noodp" />
        <meta http-equiv="X-UA-Compatible" content="IE=edge, chrome=1">
        <title>Kettle循环分页导入数据 - 豆是不吃豆豉</title><meta name="Description" content="豆是不吃豆豉"><meta property="og:title" content="Kettle循环分页导入数据" />
<meta property="og:description" content="需求 将某新闻系统中某一个时间点之前的全量数据导入到新的数据库。由于系统很老旧，有很多年了，并且新闻内容是存储在数据库中的，数据量约在五十万左" />
<meta property="og:type" content="article" />
<meta property="og:url" content="https://dou-chi.gitee.io/kettle-paging/" />
<meta property="og:image" content="https://dou-chi.gitee.io/logo.png"/>
<meta property="article:published_time" content="2021-01-14T19:27:04+08:00" />
<meta property="article:modified_time" content="2021-01-14T19:27:04+08:00" />
<meta name="twitter:card" content="summary_large_image"/>
<meta name="twitter:image" content="https://dou-chi.gitee.io/logo.png"/>

<meta name="twitter:title" content="Kettle循环分页导入数据"/>
<meta name="twitter:description" content="需求 将某新闻系统中某一个时间点之前的全量数据导入到新的数据库。由于系统很老旧，有很多年了，并且新闻内容是存储在数据库中的，数据量约在五十万左"/>
<meta name="application-name" content="豆是不吃豆豉">
<meta name="apple-mobile-web-app-title" content="豆是不吃豆豉"><meta name="theme-color" content="#ffffff"><meta name="msapplication-TileColor" content="#da532c"><link rel="shortcut icon" type="image/x-icon" href="/favicon.ico" />
        <link rel="icon" type="image/png" sizes="32x32" href="/favicon-32x32.png">
        <link rel="icon" type="image/png" sizes="16x16" href="/favicon-16x16.png"><link rel="apple-touch-icon" sizes="180x180" href="/apple-touch-icon.png"><link rel="mask-icon" href="/safari-pinned-tab.svg" color="#5bbad5"><link rel="manifest" href="/site.webmanifest"><link rel="canonical" href="https://dou-chi.gitee.io/kettle-paging/" /><link rel="prev" href="https://dou-chi.gitee.io/actdynamicprocess/" /><link rel="next" href="https://dou-chi.gitee.io/jenkins-auto-test/" /><link rel="stylesheet" href="/lib/normalize/normalize.min.css"><link rel="stylesheet" href="/css/style.min.css"><link rel="stylesheet" href="/lib/fontawesome-free/all.min.css"><link rel="stylesheet" href="/lib/animate/animate.min.css"><script type="application/ld+json">
    {
        "@context": "http://schema.org",
        "@type": "BlogPosting",
        "headline": "Kettle循环分页导入数据",
        "inLanguage": "zh-CN",
        "mainEntityOfPage": {
            "@type": "WebPage",
            "@id": "https:\/\/dou-chi.gitee.io\/kettle-paging\/"
        },"genre": "posts","keywords": "Kettle","wordcount":  1320 ,
        "url": "https:\/\/dou-chi.gitee.io\/kettle-paging\/","datePublished": "2021-01-14T19:27:04+08:00","dateModified": "2021-01-14T19:27:04+08:00","publisher": {
            "@type": "Organization",
            "name": ""},"author": {
                "@type": "Person",
                "name": "豆是不吃豆豉"
            },"description": ""
    }
    </script></head>
    <body header-desktop="fixed" header-mobile="auto"><script type="text/javascript">(window.localStorage && localStorage.getItem('theme') ? localStorage.getItem('theme') === 'dark' : ('dark' === 'auto' ? window.matchMedia('(prefers-color-scheme: dark)').matches : 'dark' === 'dark')) && document.body.setAttribute('theme', 'dark');</script>

        <div id="mask"></div><div class="wrapper"><header class="desktop" id="header-desktop">
    <div class="header-wrapper">
        <div class="header-title">
            <a href="/" title="豆是不吃豆豉">豆是不吃豆豉</a>
        </div>
        <div class="menu">
            <div class="menu-inner"><a class="menu-item" href="/posts/"> 所有文章 </a><a class="menu-item" href="/tags/"> 标签 </a><a class="menu-item" href="/categories/"> 分类 </a><a class="menu-item" href="https://github.com/dou-chi" rel="noopener noreffer" target="_blank"> github </a><a class="menu-item" href="https://gitee.com/dou-chi" rel="noopener noreffer" target="_blank"> gitee </a><span class="menu-item delimiter"></span><span class="menu-item search" id="search-desktop">
                        <input type="text" placeholder="搜索文章标题或内容..." id="search-input-desktop">
                        <a href="javascript:void(0);" class="search-button search-toggle" id="search-toggle-desktop" title="搜索">
                            <i class="fas fa-search fa-fw"></i>
                        </a>
                        <a href="javascript:void(0);" class="search-button search-clear" id="search-clear-desktop" title="清空">
                            <i class="fas fa-times-circle fa-fw"></i>
                        </a>
                        <span class="search-button search-loading" id="search-loading-desktop">
                            <i class="fas fa-spinner fa-fw fa-spin"></i>
                        </span>
                    </span><a href="javascript:void(0);" class="menu-item theme-switch" title="切换主题">
                    <i class="fas fa-adjust fa-fw"></i>
                </a>
            </div>
        </div>
    </div>
</header><header class="mobile" id="header-mobile">
    <div class="header-container">
        <div class="header-wrapper">
            <div class="header-title">
                <a href="/" title="豆是不吃豆豉">豆是不吃豆豉</a>
            </div>
            <div class="menu-toggle" id="menu-toggle-mobile">
                <span></span><span></span><span></span>
            </div>
        </div>
        <div class="menu" id="menu-mobile"><div class="search-wrapper">
                    <div class="search mobile" id="search-mobile">
                        <input type="text" placeholder="搜索文章标题或内容..." id="search-input-mobile">
                        <a href="javascript:void(0);" class="search-button search-toggle" id="search-toggle-mobile" title="搜索">
                            <i class="fas fa-search fa-fw"></i>
                        </a>
                        <a href="javascript:void(0);" class="search-button search-clear" id="search-clear-mobile" title="清空">
                            <i class="fas fa-times-circle fa-fw"></i>
                        </a>
                        <span class="search-button search-loading" id="search-loading-mobile">
                            <i class="fas fa-spinner fa-fw fa-spin"></i>
                        </span>
                    </div>
                    <a href="javascript:void(0);" class="search-cancel" id="search-cancel-mobile">
                        取消
                    </a>
                </div><a class="menu-item" href="/posts/" title="">所有文章</a><a class="menu-item" href="/tags/" title="">标签</a><a class="menu-item" href="/categories/" title="">分类</a><a class="menu-item" href="https://github.com/dou-chi" title="" rel="noopener noreffer" target="_blank">github</a><a class="menu-item" href="https://gitee.com/dou-chi" title="" rel="noopener noreffer" target="_blank">gitee</a><a href="javascript:void(0);" class="menu-item theme-switch" title="切换主题">
                <i class="fas fa-adjust fa-fw"></i>
            </a></div>
    </div>
</header>
<div class="search-dropdown desktop">
    <div id="search-dropdown-desktop"></div>
</div>
<div class="search-dropdown mobile">
    <div id="search-dropdown-mobile"></div>
</div>
<main class="main">
                <div class="container"><div class="toc" id="toc-auto">
            <h2 class="toc-title">目录</h2>
            <div class="toc-content always-active" id="toc-content-auto"></div>
        </div><article class="page single"><h1 class="single-title animated flipInX">Kettle循环分页导入数据</h1><div class="post-meta">
            <div class="post-meta-line"><span class="post-author"><a href="/" title="Author" rel=" author" class="author"><i class="fas fa-user-circle fa-fw"></i>豆是不吃豆豉</a></span>&nbsp;<span class="post-category">收录于 <a href="/categories/kettle/"><i class="far fa-folder fa-fw"></i>Kettle</a></span></div>
            <div class="post-meta-line"><i class="far fa-calendar-alt fa-fw"></i>&nbsp;<time datetime="2021-01-14">2021-01-14</time>&nbsp;<i class="fas fa-pencil-alt fa-fw"></i>&nbsp;约 1320 字&nbsp;
                <i class="far fa-clock fa-fw"></i>&nbsp;预计阅读 3 分钟&nbsp;</div>
        </div><div class="details toc" id="toc-static"  kept="">
                <div class="details-summary toc-title">
                    <span>目录</span>
                    <span><i class="details-icon fas fa-angle-right"></i></span>
                </div>
                <div class="details-content toc-content" id="toc-content-static"><nav id="TableOfContents">
  <ul>
    <li>
      <ul>
        <li><a href="#需求">需求</a></li>
        <li><a href="#kettle-下载">Kettle 下载</a></li>
        <li><a href="#实现">实现</a>
          <ul>
            <li><a href="#测试数据">测试数据</a></li>
            <li><a href="#kettle转换">Kettle转换</a>
              <ul>
                <li><a href="#获取总条数转换">获取总条数转换</a></li>
                <li><a href="#初始化分页变量">初始化分页变量</a></li>
                <li><a href="#设置总条数到作业变量">设置总条数到作业变量</a></li>
                <li><a href="#校验是否到达总页数">校验是否到达总页数</a></li>
                <li><a href="#分页转换">分页转换</a></li>
                <li><a href="#跳转下一页">跳转下一页</a></li>
                <li><a href="#启动">启动</a></li>
              </ul>
            </li>
            <li><a href="#扩展">扩展</a></li>
          </ul>
        </li>
      </ul>
    </li>
  </ul>
</nav></div>
            </div><div class="content" id="content"><h2 id="需求">需求</h2>
<p>将某新闻系统中某一个时间点之前的全量数据导入到新的数据库。由于系统很老旧，有很多年了，并且新闻内容是存储在数据库中的，数据量约在五十万左右。</p>
<p><code>为什么要使用Kettle</code>：源库中存在大字段，数据量五十万左右，直接使用<code>Navicat</code>数据传输功能，跳板机CPU和内存直接占满卡死，导入失败，并且使用<code>Navicat</code>无法对一些特别的数据进行个性化处理。所以想到使用<code>Kettle</code>循环分页的抽取数据，每次抽取一部分就不会把跳板机搞宕机。</p>
<h2 id="kettle-下载">Kettle 下载</h2>
<p>官方：<a href="https://sourceforge.net/projects/pentaho/files/" target="_blank" rel="noopener noreffer">https://sourceforge.net/projects/pentaho/files/</a></p>
<p>国内镜像(目前只到8.2版本)：<a href="http://mirror.bit.edu.cn/pentaho/" target="_blank" rel="noopener noreffer">http://mirror.bit.edu.cn/pentaho/</a></p>
<h2 id="实现">实现</h2>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://i.loli.net/2021/01/14/SCRnAqOlI592GiB.png"
        data-srcset="https://i.loli.net/2021/01/14/SCRnAqOlI592GiB.png, https://i.loli.net/2021/01/14/SCRnAqOlI592GiB.png 1.5x, https://i.loli.net/2021/01/14/SCRnAqOlI592GiB.png 2x"
        data-sizes="auto"
        alt="https://i.loli.net/2021/01/14/SCRnAqOlI592GiB.png"
        title="kettle-page-1.png" /></p>
<h3 id="测试数据">测试数据</h3>
<p>原数据有隐私问题，不方便展示。这里使用新建一个测试数据。</p>
<p>测试表</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-sql" data-lang="sql"><span class="o">#</span> <span class="err">源表</span>
<span class="k">CREATE</span> <span class="k">TABLE</span> <span class="o">`</span><span class="n">t_news</span><span class="o">`</span> <span class="p">(</span>
  <span class="o">`</span><span class="n">id</span><span class="o">`</span> <span class="nb">int</span><span class="p">(</span><span class="mi">11</span><span class="p">)</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="n">AUTO_INCREMENT</span><span class="p">,</span>
  <span class="o">`</span><span class="n">title</span><span class="o">`</span> <span class="nb">varchar</span><span class="p">(</span><span class="mi">200</span><span class="p">)</span> <span class="k">DEFAULT</span> <span class="k">NULL</span><span class="p">,</span>
  <span class="o">`</span><span class="n">content</span><span class="o">`</span> <span class="nb">varchar</span><span class="p">(</span><span class="mi">1000</span><span class="p">)</span> <span class="k">DEFAULT</span> <span class="k">NULL</span><span class="p">,</span>
  <span class="o">`</span><span class="n">create_time</span><span class="o">`</span> <span class="n">datetime</span> <span class="k">DEFAULT</span> <span class="k">NULL</span><span class="p">,</span>
  <span class="k">PRIMARY</span> <span class="k">KEY</span> <span class="p">(</span><span class="o">`</span><span class="n">id</span><span class="o">`</span><span class="p">)</span>
<span class="p">)</span> <span class="n">ENGINE</span><span class="o">=</span><span class="n">InnoDB</span> <span class="n">AUTO_INCREMENT</span><span class="o">=</span><span class="mi">11</span> <span class="k">DEFAULT</span> <span class="n">CHARSET</span><span class="o">=</span><span class="n">utf8mb4</span> <span class="k">COLLATE</span><span class="o">=</span><span class="n">utf8mb4_0900_ai_ci</span><span class="p">;</span>

<span class="o">#</span> <span class="err">目标表</span>
<span class="k">CREATE</span> <span class="k">TABLE</span> <span class="o">`</span><span class="n">t_news_2</span><span class="o">`</span> <span class="p">(</span>
  <span class="o">`</span><span class="n">id</span><span class="o">`</span> <span class="nb">int</span><span class="p">(</span><span class="mi">11</span><span class="p">)</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="n">AUTO_INCREMENT</span><span class="p">,</span>
  <span class="o">`</span><span class="n">title</span><span class="o">`</span> <span class="nb">varchar</span><span class="p">(</span><span class="mi">200</span><span class="p">)</span> <span class="k">DEFAULT</span> <span class="k">NULL</span><span class="p">,</span>
  <span class="o">`</span><span class="n">content</span><span class="o">`</span> <span class="nb">varchar</span><span class="p">(</span><span class="mi">1000</span><span class="p">)</span> <span class="k">DEFAULT</span> <span class="k">NULL</span><span class="p">,</span>
  <span class="o">`</span><span class="n">create_time</span><span class="o">`</span> <span class="n">datetime</span> <span class="k">DEFAULT</span> <span class="k">NULL</span><span class="p">,</span>
  <span class="k">PRIMARY</span> <span class="k">KEY</span> <span class="p">(</span><span class="o">`</span><span class="n">id</span><span class="o">`</span><span class="p">)</span>
<span class="p">)</span> <span class="n">ENGINE</span><span class="o">=</span><span class="n">InnoDB</span> <span class="n">AUTO_INCREMENT</span><span class="o">=</span><span class="mi">11</span> <span class="k">DEFAULT</span> <span class="n">CHARSET</span><span class="o">=</span><span class="n">utf8mb4</span> <span class="k">COLLATE</span><span class="o">=</span><span class="n">utf8mb4_0900_ai_ci</span><span class="p">;</span>

</code></pre></td></tr></table>
</div>
</div><p>创建<code>MySQL</code>函数生成测试数据·</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-sql" data-lang="sql"><span class="k">CREATE</span> <span class="k">DEFINER</span><span class="o">=`</span><span class="n">root</span><span class="o">`@`</span><span class="n">localhost</span><span class="o">`</span> <span class="k">FUNCTION</span> <span class="o">`</span><span class="n">rand_data</span><span class="o">`</span><span class="p">(</span><span class="o">`</span><span class="n">n</span><span class="o">`</span> <span class="nb">int</span><span class="p">(</span><span class="mi">11</span><span class="p">))</span> <span class="k">RETURNS</span> <span class="nb">int</span><span class="p">(</span><span class="mi">10</span><span class="p">)</span>
    <span class="k">READS</span> <span class="k">SQL</span> <span class="k">DATA</span>
<span class="k">BEGIN</span>
	<span class="k">DECLARE</span> <span class="n">str</span> <span class="nb">char</span><span class="p">(</span><span class="mi">62</span><span class="p">)</span> <span class="k">DEFAULT</span> <span class="s1">&#39;abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789&#39;</span><span class="p">;</span>
	<span class="k">DECLARE</span> <span class="n">str2</span> <span class="nb">char</span><span class="p">(</span><span class="mi">100</span><span class="p">);</span>
	<span class="k">DECLARE</span> <span class="n">i</span> <span class="nb">int</span> <span class="k">DEFAULT</span> <span class="mi">0</span><span class="p">;</span>
	<span class="n">WHILE</span> <span class="n">i</span> <span class="o">&lt;</span> <span class="n">n</span> <span class="k">DO</span>
		<span class="k">SET</span> <span class="n">str2</span> <span class="o">=</span> <span class="n">concat</span><span class="p">(</span><span class="k">substring</span><span class="p">(</span><span class="n">str</span><span class="p">,</span> <span class="mi">1</span> <span class="o">+</span> <span class="n">floor</span><span class="p">(</span><span class="n">rand</span><span class="p">()</span> <span class="o">*</span> <span class="mi">61</span><span class="p">),</span> <span class="mi">50</span><span class="p">),</span> <span class="k">substring</span><span class="p">(</span><span class="n">str</span><span class="p">,</span> <span class="mi">1</span> <span class="o">+</span> <span class="n">floor</span><span class="p">(</span><span class="n">rand</span><span class="p">()</span> <span class="o">*</span> <span class="mi">61</span><span class="p">),</span> <span class="mi">50</span><span class="p">));</span>
		<span class="k">SET</span> <span class="n">i</span> <span class="o">=</span> <span class="n">i</span> <span class="o">+</span> <span class="mi">1</span><span class="p">;</span>
		<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">t_news</span> <span class="k">VALUES</span> <span class="p">(</span><span class="k">NULL</span><span class="p">,</span> <span class="n">floor</span><span class="p">(</span><span class="n">rand</span><span class="p">()</span><span class="o">*</span><span class="n">n</span><span class="p">),</span> <span class="n">str2</span><span class="p">,</span> <span class="n">NOW</span><span class="p">());</span>
	<span class="k">END</span> <span class="n">WHILE</span><span class="p">;</span>
	<span class="k">RETURN</span> <span class="mi">0</span><span class="p">;</span>
<span class="k">END</span>
</code></pre></td></tr></table>
</div>
</div><div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-fallback" data-lang="fallback"># 运行函数生成一百万的数据
CALL rand_data(1000000);
</code></pre></td></tr></table>
</div>
</div><h3 id="kettle转换">Kettle转换</h3>
<p>数据库连接省略。</p>
<p><code>涉及到的变量</code></p>
<ul>
<li>records：待转换的总条数</li>
<li>page_start: 每页起始游标</li>
<li>page_size: 每页数量</li>
<li>page_end: 每页结束游标（MySQL用不上）</li>
</ul>
<p>总Job
<img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://i.loli.net/2021/01/14/vyTNtHOc1QYS3bu.png"
        data-srcset="https://i.loli.net/2021/01/14/vyTNtHOc1QYS3bu.png, https://i.loli.net/2021/01/14/vyTNtHOc1QYS3bu.png 1.5x, https://i.loli.net/2021/01/14/vyTNtHOc1QYS3bu.png 2x"
        data-sizes="auto"
        alt="https://i.loli.net/2021/01/14/vyTNtHOc1QYS3bu.png"
        title="kettle-page-4.png" /></p>
<h4 id="获取总条数转换">获取总条数转换</h4>
<p>新建一个转换
<img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://i.loli.net/2021/01/14/ly1oRpT4OrAU5k2.png"
        data-srcset="https://i.loli.net/2021/01/14/ly1oRpT4OrAU5k2.png, https://i.loli.net/2021/01/14/ly1oRpT4OrAU5k2.png 1.5x, https://i.loli.net/2021/01/14/ly1oRpT4OrAU5k2.png 2x"
        data-sizes="auto"
        alt="https://i.loli.net/2021/01/14/ly1oRpT4OrAU5k2.png"
        title="kettle-page-5.png" /></p>
<h4 id="初始化分页变量">初始化分页变量</h4>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://i.loli.net/2021/01/14/6UNOjkV3gqfxZEu.png"
        data-srcset="https://i.loli.net/2021/01/14/6UNOjkV3gqfxZEu.png, https://i.loli.net/2021/01/14/6UNOjkV3gqfxZEu.png 1.5x, https://i.loli.net/2021/01/14/6UNOjkV3gqfxZEu.png 2x"
        data-sizes="auto"
        alt="https://i.loli.net/2021/01/14/6UNOjkV3gqfxZEu.png"
        title="kettle-page-6.png" /></p>
<h4 id="设置总条数到作业变量">设置总条数到作业变量</h4>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://i.loli.net/2021/01/14/kzDRZo4aNgWOcE3.png"
        data-srcset="https://i.loli.net/2021/01/14/kzDRZo4aNgWOcE3.png, https://i.loli.net/2021/01/14/kzDRZo4aNgWOcE3.png 1.5x, https://i.loli.net/2021/01/14/kzDRZo4aNgWOcE3.png 2x"
        data-sizes="auto"
        alt="https://i.loli.net/2021/01/14/kzDRZo4aNgWOcE3.png"
        title="kettle-page-7.png" /></p>
<p>脚本</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-javascript" data-lang="javascript"><span class="c1">// 获取日志对象
</span><span class="c1"></span><span class="kd">var</span> <span class="nx">subject</span><span class="o">=</span><span class="s2">&#34;日志：&#34;</span><span class="p">;</span>
<span class="kd">var</span> <span class="nx">logFactory</span> <span class="o">=</span> <span class="k">new</span> <span class="nx">org</span><span class="p">.</span><span class="nx">pentaho</span><span class="p">.</span><span class="nx">di</span><span class="p">.</span><span class="nx">core</span><span class="p">.</span><span class="nx">logging</span><span class="p">.</span><span class="nx">LogChannelFactory</span><span class="p">();</span>
<span class="kd">var</span> <span class="nx">log</span><span class="o">=</span> <span class="nx">logFactory</span><span class="p">.</span><span class="nx">create</span><span class="p">(</span><span class="nx">subject</span><span class="p">);</span>
<span class="c1">// 获取查询出来的总条数
</span><span class="c1"></span><span class="kd">var</span> <span class="nx">count</span> <span class="o">=</span> <span class="nx">parent_job</span><span class="p">.</span><span class="nx">getVariable</span><span class="p">(</span><span class="s2">&#34;records&#34;</span><span class="p">);</span>
<span class="c1">// 设置到job中
</span><span class="c1"></span><span class="nx">parent_job</span><span class="p">.</span><span class="nx">setVariable</span><span class="p">(</span><span class="s2">&#34;records&#34;</span><span class="p">,</span><span class="nb">parseInt</span><span class="p">(</span><span class="nx">count</span><span class="p">));</span>
<span class="nx">log</span><span class="p">.</span><span class="nx">logMinimal</span><span class="p">(</span><span class="s2">&#34;=================================总记录数为====&#34;</span><span class="o">+</span> <span class="nx">count</span><span class="p">);</span>
<span class="kc">true</span><span class="p">;</span>
</code></pre></td></tr></table>
</div>
</div><h4 id="校验是否到达总页数">校验是否到达总页数</h4>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://i.loli.net/2021/01/14/G18XqLFyeliKOD7.png"
        data-srcset="https://i.loli.net/2021/01/14/G18XqLFyeliKOD7.png, https://i.loli.net/2021/01/14/G18XqLFyeliKOD7.png 1.5x, https://i.loli.net/2021/01/14/G18XqLFyeliKOD7.png 2x"
        data-sizes="auto"
        alt="https://i.loli.net/2021/01/14/G18XqLFyeliKOD7.png"
        title="kettle-page-8.png" /></p>
<h4 id="分页转换">分页转换</h4>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://i.loli.net/2021/01/14/dKJfhNXnqMmSeCg.png"
        data-srcset="https://i.loli.net/2021/01/14/dKJfhNXnqMmSeCg.png, https://i.loli.net/2021/01/14/dKJfhNXnqMmSeCg.png 1.5x, https://i.loli.net/2021/01/14/dKJfhNXnqMmSeCg.png 2x"
        data-sizes="auto"
        alt="https://i.loli.net/2021/01/14/dKJfhNXnqMmSeCg.png"
        title="kettle-page-9.png" /></p>
<h4 id="跳转下一页">跳转下一页</h4>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://i.loli.net/2021/01/14/dBqOKrRtbivHuCe.png"
        data-srcset="https://i.loli.net/2021/01/14/dBqOKrRtbivHuCe.png, https://i.loli.net/2021/01/14/dBqOKrRtbivHuCe.png 1.5x, https://i.loli.net/2021/01/14/dBqOKrRtbivHuCe.png 2x"
        data-sizes="auto"
        alt="https://i.loli.net/2021/01/14/dBqOKrRtbivHuCe.png"
        title="kettle-page-10.png" /></p>
<p>脚本</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span><span class="lnt">19
</span><span class="lnt">20
</span><span class="lnt">21
</span><span class="lnt">22
</span><span class="lnt">23
</span><span class="lnt">24
</span><span class="lnt">25
</span><span class="lnt">26
</span><span class="lnt">27
</span><span class="lnt">28
</span><span class="lnt">29
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-javascript" data-lang="javascript">
<span class="c1">// 获取日志
</span><span class="c1"></span><span class="kd">var</span> <span class="nx">subject</span><span class="o">=</span><span class="s2">&#34;跳转下一个分页&#34;</span><span class="p">;</span>
<span class="kd">var</span> <span class="nx">logFactory</span> <span class="o">=</span> <span class="k">new</span> <span class="nx">org</span><span class="p">.</span><span class="nx">pentaho</span><span class="p">.</span><span class="nx">di</span><span class="p">.</span><span class="nx">core</span><span class="p">.</span><span class="nx">logging</span><span class="p">.</span><span class="nx">LogChannelFactory</span><span class="p">();</span>
<span class="kd">var</span> <span class="nx">log</span><span class="o">=</span> <span class="nx">logFactory</span><span class="p">.</span><span class="nx">create</span><span class="p">(</span><span class="nx">subject</span><span class="p">);</span>
<span class="c1">// 获取开始游标
</span><span class="c1"></span><span class="kd">var</span> <span class="nx">page_start</span> <span class="o">=</span> <span class="nx">parent_job</span><span class="p">.</span><span class="nx">getVariable</span><span class="p">(</span><span class="s2">&#34;page_start&#34;</span><span class="p">);</span>

<span class="c1">// 结束游标 （MySQL可以忽略）
</span><span class="c1"></span><span class="kd">var</span> <span class="nx">page_end</span> <span class="o">=</span> <span class="nx">parent_job</span><span class="p">.</span><span class="nx">getVariable</span><span class="p">(</span><span class="s2">&#34;page_end&#34;</span><span class="p">);</span>
<span class="c1">// 每页数
</span><span class="c1"></span><span class="kd">var</span> <span class="nx">page_size</span> <span class="o">=</span> <span class="nx">parent_job</span><span class="p">.</span><span class="nx">getVariable</span><span class="p">(</span><span class="s2">&#34;page_size&#34;</span><span class="p">);</span>
<span class="c1">// 总条数
</span><span class="c1"></span><span class="kd">var</span> <span class="nx">records</span> <span class="o">=</span> <span class="nx">parent_job</span><span class="p">.</span><span class="nx">getVariable</span><span class="p">(</span><span class="s2">&#34;records&#34;</span><span class="p">);</span>
<span class="nx">log</span><span class="p">.</span><span class="nx">logMinimal</span><span class="p">(</span><span class="s2">&#34;==========每页条数==&#34;</span><span class="o">+</span><span class="nx">page_size</span> <span class="o">+</span><span class="s2">&#34;========总条数===&#34;</span><span class="o">+</span><span class="nx">records</span><span class="o">+</span><span class="s2">&#34;========已经完成==&#34;</span><span class="o">+</span> <span class="nx">page_start</span> <span class="o">+</span> <span class="s2">&#34;--到--&#34;</span> <span class="o">+</span> <span class="nx">page_end</span><span class="p">);</span>
<span class="c1">// 如果转换完了，直接结束
</span><span class="c1"></span><span class="k">if</span><span class="p">(</span><span class="nb">Number</span><span class="p">(</span><span class="nx">page_start</span><span class="p">)</span> <span class="o">&gt;</span> <span class="nb">Number</span><span class="p">(</span><span class="nx">records</span><span class="p">)){</span>
	<span class="kc">false</span><span class="p">;</span>
<span class="p">}</span> <span class="k">else</span> <span class="p">{</span>

	<span class="c1">// 开始游标
</span><span class="c1"></span>	<span class="nx">page_start</span> <span class="o">=</span> <span class="nb">Number</span><span class="p">(</span><span class="nx">page_start</span><span class="p">)</span> <span class="o">+</span> <span class="nb">Number</span><span class="p">(</span><span class="nx">page_size</span><span class="p">);</span>
	<span class="nx">parent_job</span><span class="p">.</span><span class="nx">setVariable</span><span class="p">(</span><span class="s2">&#34;page_start&#34;</span><span class="p">,</span> <span class="nx">page_start</span><span class="p">);</span>
    <span class="c1">// 结束游标（MySQL可以忽略）
</span><span class="c1"></span>	<span class="nx">page_end</span> <span class="o">=</span> <span class="nb">Number</span><span class="p">(</span><span class="nx">page_end</span><span class="p">)</span> <span class="o">+</span> <span class="nb">Number</span><span class="p">(</span><span class="nx">page_size</span><span class="p">);</span>
	<span class="nx">parent_job</span><span class="p">.</span><span class="nx">setVariable</span><span class="p">(</span><span class="s2">&#34;page_end&#34;</span><span class="p">,</span> <span class="nx">page_end</span><span class="p">);</span>
	<span class="nx">log</span><span class="p">.</span><span class="nx">logMinimal</span><span class="p">(</span><span class="s2">&#34;==========每页条数==&#34;</span><span class="o">+</span><span class="nx">page_size</span> <span class="o">+</span><span class="s2">&#34;========总条数===&#34;</span><span class="o">+</span><span class="nx">records</span><span class="o">+</span><span class="s2">&#34;========即将开始==&#34;</span><span class="o">+</span> <span class="nx">page_start</span> <span class="o">+</span> <span class="s2">&#34;--到--&#34;</span> <span class="o">+</span> <span class="nx">page_end</span><span class="p">);</span>	
	<span class="kc">true</span><span class="p">;</span>
<span class="p">}</span>
</code></pre></td></tr></table>
</div>
</div><h4 id="启动">启动</h4>
<p>启动后我们就可以看到日志信息，数据就已经开始进行转换。</p>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://i.loli.net/2021/01/14/uoFHODrNq4YptbT.png"
        data-srcset="https://i.loli.net/2021/01/14/uoFHODrNq4YptbT.png, https://i.loli.net/2021/01/14/uoFHODrNq4YptbT.png 1.5x, https://i.loli.net/2021/01/14/uoFHODrNq4YptbT.png 2x"
        data-sizes="auto"
        alt="https://i.loli.net/2021/01/14/uoFHODrNq4YptbT.png"
        title="kettle-page-11.png" /></p>
<h3 id="扩展">扩展</h3>
<p>以此类推，我们可以给这个<code>Job</code>设置定时执行，可以动态获取每天的时间，查询当天的数据进行增量的更新。</p>
</div><div class="post-footer" id="post-footer">
    <div class="post-info">
        <div class="post-info-line">
            <div class="post-info-mod">
                <span>更新于 2021-01-14</span>
            </div>
            <div class="post-info-license"></div>
        </div>
        <div class="post-info-line">
            <div class="post-info-md"><span>
                            <a class="link-to-markdown" href="/kettle-paging/index.md" target="_blank">阅读原始文档</a>
                        </span></div>
            <div class="post-info-share">
                <span><a href="javascript:void(0);" title="分享到 Twitter" data-sharer="twitter" data-url="https://dou-chi.gitee.io/kettle-paging/" data-title="Kettle循环分页导入数据" data-hashtags="Kettle"><i class="fab fa-twitter fa-fw"></i></a><a href="javascript:void(0);" title="分享到 Facebook" data-sharer="facebook" data-url="https://dou-chi.gitee.io/kettle-paging/" data-hashtag="Kettle"><i class="fab fa-facebook-square fa-fw"></i></a><a href="javascript:void(0);" title="分享到 WhatsApp" data-sharer="whatsapp" data-url="https://dou-chi.gitee.io/kettle-paging/" data-title="Kettle循环分页导入数据" data-web><i class="fab fa-whatsapp fa-fw"></i></a><a href="javascript:void(0);" title="分享到 Line" data-sharer="line" data-url="https://dou-chi.gitee.io/kettle-paging/" data-title="Kettle循环分页导入数据"><i data-svg-src="/lib/simple-icons/icons/line.min.svg"></i></a><a href="javascript:void(0);" title="分享到 微博" data-sharer="weibo" data-url="https://dou-chi.gitee.io/kettle-paging/" data-title="Kettle循环分页导入数据"><i class="fab fa-weibo fa-fw"></i></a><a href="javascript:void(0);" title="分享到 Myspace" data-sharer="myspace" data-url="https://dou-chi.gitee.io/kettle-paging/" data-title="Kettle循环分页导入数据" data-description=""><i data-svg-src="/lib/simple-icons/icons/myspace.min.svg"></i></a><a href="javascript:void(0);" title="分享到 Blogger" data-sharer="blogger" data-url="https://dou-chi.gitee.io/kettle-paging/" data-title="Kettle循环分页导入数据" data-description=""><i class="fab fa-blogger fa-fw"></i></a><a href="javascript:void(0);" title="分享到 Evernote" data-sharer="evernote" data-url="https://dou-chi.gitee.io/kettle-paging/" data-title="Kettle循环分页导入数据"><i class="fab fa-evernote fa-fw"></i></a></span>
            </div>
        </div>
    </div>

    <div class="post-info-more">
        <section class="post-tags"><i class="fas fa-tags fa-fw"></i>&nbsp;<a href="/tags/kettle/">Kettle</a></section>
        <section>
            <span><a href="javascript:void(0);" onclick="window.history.back();">返回</a></span>&nbsp;|&nbsp;<span><a href="/">主页</a></span>
        </section>
    </div>

    <div class="post-nav"><a href="/actdynamicprocess/" class="prev" rel="prev" title="在Activiti中实现动态流程（修改流程图重新发布后对正在运行的流程生效）"><i class="fas fa-angle-left fa-fw"></i>在Activiti中实现动态流程（修改流程图重新发布后对正在运行的流程生效）</a>
            <a href="/jenkins-auto-test/" class="next" rel="next" title="Jenkins Docker 用与测试环境的自动打包Vue项目和SpringBoot项目">Jenkins Docker 用与测试环境的自动打包Vue项目和SpringBoot项目<i class="fas fa-angle-right fa-fw"></i></a></div>
</div>
<div id="comments"></div></article></div>
            </main><footer class="footer">
        <div class="footer-container"><div class="footer-line">由 <a href="https://gohugo.io/" target="_blank" rel="noopener noreffer" title="Hugo 0.74.3">Hugo</a> 强力驱动 | 主题 - <a href="https://github.com/dillonzq/LoveIt" target="_blank" rel="noopener noreffer" title="LoveIt 0.2.10"><i class="far fa-kiss-wink-heart fa-fw"></i> LoveIt</a>
                </div><div class="footer-line"><i class="far fa-copyright fa-fw"></i><span itemprop="copyrightYear">2019 - 2021</span><span class="author" itemprop="copyrightHolder">&nbsp;<a href="/" target="_blank">豆是不吃豆豉</a></span></div>
        </div>
    </footer></div>

        <div id="fixed-buttons"><a href="#" id="back-to-top" class="fixed-button" title="回到顶部">
                <i class="fas fa-arrow-up fa-fw"></i>
            </a><a href="#" id="view-comments" class="fixed-button" title="查看评论">
                <i class="fas fa-comment fa-fw"></i>
            </a>
        </div><link rel="stylesheet" href="/lib/katex/katex.min.css"><link rel="stylesheet" href="/lib/katex/copy-tex.min.css"><link rel="stylesheet" href="/lib/cookieconsent/cookieconsent.min.css"><script type="text/javascript" src="/lib/smooth-scroll/smooth-scroll.min.js"></script><script type="text/javascript" src="/lib/autocomplete/autocomplete.min.js"></script><script type="text/javascript" src="/lib/lunr/lunr.min.js"></script><script type="text/javascript" src="/lib/lunr/lunr.stemmer.support.min.js"></script><script type="text/javascript" src="/lib/lunr/lunr.zh.min.js"></script><script type="text/javascript" src="/lib/lazysizes/lazysizes.min.js"></script><script type="text/javascript" src="/lib/clipboard/clipboard.min.js"></script><script type="text/javascript" src="/lib/sharer/sharer.min.js"></script><script type="text/javascript" src="/lib/katex/katex.min.js"></script><script type="text/javascript" src="/lib/katex/auto-render.min.js"></script><script type="text/javascript" src="/lib/katex/copy-tex.min.js"></script><script type="text/javascript" src="/lib/katex/mhchem.min.js"></script><script type="text/javascript" src="/lib/cookieconsent/cookieconsent.min.js"></script><script type="text/javascript">window.config={"code":{"copyTitle":"复制到剪贴板","maxShownLines":100},"comment":{},"cookieconsent":{"content":{"dismiss":"同意","link":"了解更多","message":"本网站使用 Cookies 来改善您的浏览体验."},"enable":true,"palette":{"button":{"background":"#f0f0f0"},"popup":{"background":"#1aa3ff"}},"theme":"edgeless"},"math":{"delimiters":[{"display":true,"left":"$$","right":"$$"},{"display":true,"left":"\\[","right":"\\]"},{"display":false,"left":"$","right":"$"},{"display":false,"left":"\\(","right":"\\)"}],"strict":false},"search":{"highlightTag":"em","lunrIndexURL":"/index.json","lunrLanguageCode":"zh","lunrSegmentitURL":"/lib/lunr/lunr.segmentit.js","maxResultLength":10,"noResultsFound":"没有找到结果","snippetLength":50,"type":"lunr"}};</script><script type="text/javascript" src="/js/theme.min.js"></script></body>
</html>
